package org.easyspring.util;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

public class ExcelTojsonUtil {
    //json的key
    private static String[] titleArray = {"id","poiId"};


    public static void main(String[] args) {

        InputStream is = null;
        String excelToJson = excelToJson(is);
        try {
            createJsonFile(excelToJson);
        } catch (IOException e) {
// TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static String excelToJson(InputStream is) {
        JSONObject jsonRow = new JSONObject();
        try {
            // 获取文件
            is = new FileInputStream("C:\\Users\\15012\\Desktop\\city.xlsx");
            // 创建excel工作空间
            Workbook workbook = WorkbookFactory.create(is);
            // 获取sheet页的数量
            int sheetCount = workbook.getNumberOfSheets();
            // 遍历每个sheet页
            for (int i = 0; i < sheetCount; i++) {
                // 获取每个sheet页
                Sheet sheet = workbook.getSheetAt(i);
               // 获取sheet页的总行数
                int rowCount = sheet.getPhysicalNumberOfRows();
               // 遍历每一行
                Map<String, Integer> map = new HashMap<String, Integer>();
                for (int j = 0; j < rowCount; j++) {
                    //记录每一行的一个唯一标识作为json的key
                    String key = "";
                   // 获取列对象
                    Row row = sheet.getRow(j);
                    // 获取总列数
                    int cellCount = row.getPhysicalNumberOfCells();
                    //除去第一行的标题
                    if (j > 0) {
                       // 遍历所有数据
                        for (int n = 0; n < cellCount; n++) {
                            // 获取每一个单元格
                            Cell cell = row.getCell(n);
                             // 在读取单元格内容前,设置所有单元格中内容都是字符串类型
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            // 按照字符串类型读取单元格内数据
                            String cellValue = cell.getStringCellValue();
                            if (n == 1) {
                                key = cellValue;
                            }
                            map.put(j+"", Integer.parseInt(cellValue));
                        }
                    }
                }
                if (map.size() != 0) {
                    Collection<Integer> values = map.values();
                    String join = StringUtils.join(",", values);
                    jsonRow.put("ids", values);
                }

            }
            System.out.println(jsonRow.toString());
        } catch (Exception e) {

        }
        return jsonRow.toString();

    }


    public static void createJsonFile(String jsonData) throws IOException {
        File file = new File("D:\\test.json");
//连接流
        FileOutputStream fos = null;
//链接流
        BufferedOutputStream bos = null;
        try {
            fos = new FileOutputStream(file);
            bos = new BufferedOutputStream(fos);
            bos.write(jsonData.getBytes());
            ;
            bos.flush();

        } catch (FileNotFoundException e) {
// TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (null != bos) {
                bos.close();
            }
            if (null != fos) {
                fos.close();
            }
        }
    }
}
